SQL Stored Procedures

Posted on |

1.1 Creating a Stored Procedure

1
2
3
4
5
6
7
create procedure dbo.spPeople_GetAll    # use alter procedure for altering 
as 
begin
	set nocount on;    # do not show how many rows are affected
	select id, FirstName, LastName
	from dbo.People;
end

1.2 Calling a Stored Procedure

Direct call (e.g. select id, FirstName, LastName from dbo.People) has to be compiled then run, but a stored procedure is always compiled. It will be more efficient to call a stored procedure. 

1
exec dbo.spPeople_GetAll

2.1 Creating a Stored Procedure to Sort by Last Name

1
2
3
4
5
6
7
8
create procedure dbo.spPeople_GetByLastName
	@LastName nvarchar(50)
as 
begin
	select id, FirstName, LastName
	from dbo.People
	where LastName = @LastName
end

2.2 Calling a Stored Procedure to Get People with Same Last Name

Last name was passed in as a variable

1
exec dbo.spPeople_GetByLastName 'Corey'

3.1 Creating a Stored Procedure to Sort by Last Name and First Name

1
2
3
4
5
6
7
8
9
create procedure dbo.spPeople_GetByLastName
	@LastName nvarchar(50),
	@FirstName nvarchar(50)    # add a default value: @FirstName nvarchar(50) = ''
as 
begin
	select id, FirstName, LastName
	from dbo.People
	where LastName = @LastName and FirstName = @FirstName;
end

3.2 Calling a Stored Procedure to Get People with Same Last Name and First Name

1
exec dbo.spPeople_GetByLastName 'Corey','Tim'